﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Data;
using System.Reflection;
using System.Data.SqlClient;

namespace DAO
{
    /// <summary>
    /// Factory Type enumeration
    /// </summary>
    public enum FACTORYTYPE
    {
        APPOINTMETSFACTORY = 1,
        APPSTATUSFACTORY = 2,
        DIETNUTRITIOUSHISTORYFACTORY = 3,
        DOCTORSFACTORY = 4,
        GENERALEXAMINATIONFACTORY = 5,
        MEDICALRECORDFACTORY = 6,
        MODULEFACTORY = 7,
        PARIKSHAFACTORY = 8,
        PATIENTFACTORY = 9,
        PERSONALHISTORYFACTORY = 10,
        PRIVILEGIESFACTORY = 11,
        TREATMENTSPLANFACTORY = 12,
        USERSFACTORY = 13,
        APPTYPEFACTORY = 14,
        PRODUCTSFACTORY = 15,
        ORDERFACTORY = 16,
        ORDERPRODUCTS = 17
    };
    public abstract class DAOFactory
    {
        private static Dictionary<string, string> QueryDictionary = new Dictionary<string,string>();
        /// <summary>
        /// Creating Factory's
        /// </summary>
        /// <param name="factoryType">Factory Type</param>
        /// <returns></returns>
        public static DAOFactory getFactory(FACTORYTYPE factoryType)
        {
            switch (factoryType)
            {
                default: return null;
            }

        }

        /// <summary>
        /// Abstract method for creating object
        /// </summary>
        /// <returns></returns>
        public abstract object createObject();

        /// <summary>
        /// Load all objects from a table
        /// </summary>
        /// <param name="type">Type of the object</param>
        /// <param name="criteria">[] criteria in the where clause</param>
        /// <param name="values">[] values in the where clause</param>
        /// <param name="start">Start record</param>
        /// <param name="limit">Limit record</param>
        /// <returns></returns>
        public DataSet LoadAll(Type type, string[] criteria, string[] values, int start, int limit, string qName)
        {
            string q;
            
            int len = 0;
            if (criteria != null)
                len = criteria.Length;

            SqlParameter[] spList = new SqlParameter[len];

            if (!QueryDictionary.TryGetValue(qName, out q))
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("SELECT ");
                string dbTableName = type.Name;
                string props = "";
                foreach (PropertyInfo pi in type.GetProperties())
                {
                    props += dbTableName + "." + pi.Name + ",";
                }

                sb.Append(props.Substring(0, props.Length - 1));
                bool num = start != limit && start < limit;
                if (num)
                {
                    sb.Append("(SELECT *, ROW_NUMBER() OVER (ORDER BY ID desc) AS  rownum" +
                        "FROM " + dbTableName + ") AS a");
                }
                else
                {
                    sb.Append(" FROM " + dbTableName);
                }
                if (len > 0)
                {
                    sb.Append(" WHERE ");

                    string searchCrit = "";
                    for (int i = 0; i < len; i++)
                    {
                        searchCrit += criteria[i] + "=@par" + i + " AND ";
                    }
                    sb.Append(searchCrit.Substring(0, searchCrit.Length - 4));
                    if (num) sb.Append(" rownum >=" + start + " AND rownum<=" + limit);
                }
                else if (num) sb.Append(" WHERE rownum >=" + start + " AND rownum<=" + limit);
                q = sb.ToString();
                if(qName!="") QueryDictionary.Add(qName, q);
            }
            if (len > 0)
            {
                for (int i = 0; i < len; i++)
                {
                    spList[i] = new SqlParameter("par" + i, values[i]);
                }
            }

            return Connection.ExecuteReader(Connection.CONN_STRING1, CommandType.Text, q, spList);
        }

        public DataSet LoadAllWithLike(Type type, string[] names, string[] criteria, string[] values, int start, int limit, string qName)
        {
            int len = criteria.Length;
            SqlParameter[] spList = new SqlParameter[criteria.Length];

            string q;
            if (!QueryDictionary.TryGetValue(qName, out q))
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("SELECT ");
                string dbTableName = type.Name;
                string props = "";
                foreach (string s in names)
                {
                    props += s + ",";
                }

                sb.Append(props.Substring(0, props.Length - 1));
                bool num = start != limit && start < limit;
                if (num)
                {
                    sb.Append(" FROM (SELECT ROW_NUMBER() OVER (ORDER BY ID desc) AS  rownum," + props.Substring(0, props.Length - 1) + " " +
                        "FROM " + dbTableName + ") AS a");
                }
                else
                {
                    sb.Append(" FROM " + dbTableName);
                }

                
                if (len > 0)
                {
                    sb.Append(" WHERE ");

                    string searchCrit = "";
                    for (int i = 0; i < len; i++)
                    {
                        spList[i] = new SqlParameter("@par" + i, "%" + values[i] + "%");
                        searchCrit += criteria[i] + " LIKE @par" + i + " OR ";
                    }
                    sb.Append(searchCrit.Substring(0, searchCrit.Length - 3));
                    if (num) sb.Append(" AND rownum >=" + start + " AND rownum<=" + limit);
                }
                else if (num) sb.Append(" WHERE rownum >=" + start + " AND rownum<=" + limit);
                q = sb.ToString();
                if (qName != "") QueryDictionary.Add(qName, q);
            }
            if (len > 0)
            {
                for (int i = 0; i < len; i++)
                {
                    spList[i] = new SqlParameter("@par" + i, "%" + values[i] + "%");
                }
            }

            return Connection.ExecuteReader(Connection.CONN_STRING1, CommandType.Text, q, spList);
        }

        /// <summary>
        /// Load All objects from a table
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public DataSet LoadAll(Type t)
        {
            return LoadAll(t, null, null, 0, 0, "");
        }

        public DataSet LoadCustomSelection(Type type, string[] names, string[] criteria, string[] values, int start, int limit, string qName)
        {
            int len = 0;
            if (criteria != null)
                len = criteria.Length;
            SqlParameter[] spList = new SqlParameter[len+2];

            string q;
            if (!QueryDictionary.TryGetValue(qName, out q))
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("SELECT ");
                string dbTableName = type.Name;
                string props = "";
                foreach (string s in names)
                {
                    props += s + ",";
                }

                bool num = start != limit && start < limit;
                if (num)
                {
                    sb.Append(props.Substring(0, props.Length - 1) + ", countrow ");
                    sb.Append(" FROM (SELECT ROW_NUMBER() OVER (ORDER BY ID asc) AS  rownum, (SELECT count(ID) as qqq from "+dbTableName+") as countrow, " + props.Substring(0, props.Length - 1) +
                        " FROM " + dbTableName + ") AS a");
                }
                else
                {
                    sb.Append(props.Substring(0, props.Length - 1));
                    sb.Append(" FROM " + dbTableName);
                }
                if (len > 0)
                {
                    sb.Append(" WHERE ");

                    string searchCrit = "";
                    for (int i = 0; i < len; i++)
                    {
                        spList[i] = new SqlParameter("par" + i, values[i]);
                        searchCrit += criteria[i] + "=@par" + i + ",";
                    }
                    sb.Append(searchCrit.Substring(0, searchCrit.Length - 1));
                    if (num) sb.Append(" rownum >= @par_start AND rownum<= @par_end");
                }
                else if (num) sb.Append(" WHERE rownum >= @par_start AND rownum <= @par_end");
                q = sb.ToString();
                if (qName != "") QueryDictionary.Add(qName, q);
            }
            if (len > 0)
            {
                for (int i = 0; i < len; i++)
                {
                    spList[i] = new SqlParameter("par" + i, values[i]);
                }
            }
            spList[len++] = new SqlParameter("par_start", start);
            spList[len++] = new SqlParameter("par_end", limit);

            return Connection.ExecuteReader(Connection.CONN_STRING1, CommandType.Text, q, spList);
        }
        /// <summary>
        /// Load By query
        /// </summary>
        /// <param name="q"></param>
        /// <returns></returns>
        public DataSet LoadByQuery(string q)
        {
            return Connection.ExecuteReader(Connection.CONN_STRING1, CommandType.Text, q, null);
        }

        public DataSet LoadUnique(Type t, long id, string qName)
        {
            string q;
            if (!QueryDictionary.TryGetValue(qName, out q))
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("SELECT ");
                string dbTableName = t.Name;
                string props = "";
                foreach (PropertyInfo pi in t.GetProperties())
                {
                    props += "[" + dbTableName + "].[" + pi.Name + "],";
                }
                sb.Append(props.Substring(0, props.Length - 1));
                sb.Append(" FROM " + dbTableName);
                sb.Append(" WHERE ID=@id");
                q = sb.ToString();
                if (qName != "") QueryDictionary.Add(qName, q);
            }
            SqlParameter[] par = new SqlParameter[1];
            par[0] = new SqlParameter("id", id);

            return Connection.ExecuteReader(Connection.CONN_STRING1, CommandType.Text, q, par);
        }


        public int DeleteObjectByID(Type t, long id)
        {
            string deleteQ = "DELETE FROM "+t.Name+" WHERE ID=@id";
            SqlParameter[] par = new SqlParameter[1];
            par[0] = new SqlParameter("id", id);
            return Connection.ExecuteNonQuery(Connection.CONN_STRING1, CommandType.Text, deleteQ,false,  par);
        }

        public int DeleteObjectByManyIDs(Type t, long[] ids)
        {
            string deleteQ = "DELETE FROM " + t.Name + " WHERE ID in (";
            foreach (int id in ids)
            {
                deleteQ += id + ",";
            }
            deleteQ += deleteQ.Substring(0, deleteQ.Length-1)+")";
            return Connection.ExecuteNonQuery(Connection.CONN_STRING1, CommandType.Text, deleteQ,false, null);
        }

        public int DeleteObjectByProp(Type t, string prop, string value)
        {
            string deleteQ = "DELETE FROM " + t.Name + " WHERE "+prop+"=@val";
            SqlParameter[] par = new SqlParameter[1];
            par[0] = new SqlParameter("val", value);
            return Connection.ExecuteNonQuery(Connection.CONN_STRING1, CommandType.Text, deleteQ,false, par);
        }

        public int Insert(Type t, string[] crit, string[] vals)
        {
            StringBuilder insQ = new StringBuilder();
            insQ.Append("INSERT INTO ");
            insQ.Append(t.Name+" (");
            string namesVals = "";
            foreach (string s in crit)
            {
                namesVals += s + ",";
            }
            insQ.Append(namesVals.Substring(0, namesVals.Length - 1));

            insQ.Append(" VALUES(");
            namesVals = "";
            foreach (string v in vals)
            {
                switch (t.GetProperty(v).PropertyType.ToString())
                {
                    case "System.String":
                        namesVals += "N'"+v+"',";
                        break;
                    case "System.Int32":
                    case "System.Int64":
                        namesVals += v+",";
                        break;
                    default: namesVals += "'" + v + "',";
                        break;
                }
            }
            insQ.Append(namesVals.Substring(0, namesVals.Length - 1));
            insQ.Append(")");
            return Connection.ExecuteNonQuery(Connection.CONN_STRING1, CommandType.Text, insQ.ToString(),true, null);
        }

        public int Insert(Type t, BaseObject b)
        {
            StringBuilder insQ = new StringBuilder();
            insQ.Append("INSERT INTO ");
            insQ.Append(t.Name + " (");
            string namesVals = "";
            string names = "";
            foreach (PropertyInfo p in t.GetProperties())
            {
                if (p.Name != "ID")
                {
                    names += p.Name + ",";
                    switch (p.PropertyType.ToString())
                    {
                        case "System.String":
                            namesVals += "N'" + b.GetType().GetProperty(p.Name).GetValue(b, null) as string + "',";
                            break;
                        case "System.Int32":
                            namesVals += (Int32)b.GetType().GetProperty(p.Name).GetValue(b, null) + ",";
                            break;
                        case "System.Int64":
                            namesVals += (Int64)b.GetType().GetProperty(p.Name).GetValue(b, null) + ",";
                            break;
                        default: namesVals += "N'" + b.GetType().GetProperty(p.Name).GetValue(b, null) as string + "',";
                            break;
                    }
                }
            }
            insQ.Append(names.Substring(0, names.Length - 1));

            insQ.Append(" VALUES(");
            insQ.Append(namesVals.Substring(0, namesVals.Length - 1));
            insQ.Append(")");
            return Connection.ExecuteNonQuery(Connection.CONN_STRING1, CommandType.Text, insQ.ToString(),true, null);
        }


        public int Insert(BaseObject b)
        {
            StringBuilder insQ = new StringBuilder();
            insQ.Append("INSERT INTO [");
            Type t = b.GetType();
            insQ.Append(t.Name+"] (");
            string names = "";
            string namesVals = "";
            SqlParameter[] pars = new SqlParameter[t.GetProperties().Length-1];
            int count = 0;
            foreach (PropertyInfo p in t.GetProperties())
            {
                if (p.Name != "ID")
                {
                    names += p.Name + ",";
                    switch (p.PropertyType.ToString())
                    {
                        case "System.String":
                            pars[count] = new SqlParameter("val" + count, SqlDbType.VarChar);
                            pars[count].Value = b.GetType().GetProperty(p.Name).GetValue(b, null) as string;
                            namesVals += "@val" + count + ",";
                            count++;
                            break;
                        case "System.Int32":
                            pars[count] = new SqlParameter("val" + count, SqlDbType.Int);
                            pars[count].Value = (Int32)b.GetType().GetProperty(p.Name).GetValue(b, null);
                            namesVals += "@val" + count + ",";
                            count++;
                            break;
                        case "System.Int64":
                            long val = (Int64)b.GetType().GetProperty(p.Name).GetValue(b, null);
                            pars[count] = new SqlParameter("val" + count, SqlDbType.BigInt);
                            
                            if (val == -1)
                                pars[count].Value = DBNull.Value;
                            else
                                pars[count].Value = (Int64)b.GetType().GetProperty(p.Name).GetValue(b, null);
                            namesVals += "@val" + count + ",";
                            count++;
                            break;
                        case "System.DateTime":
                            pars[count] = new SqlParameter("val" + count, SqlDbType.DateTime);
                            pars[count].Value = b.GetType().GetProperty(p.Name).GetValue(b, null);
                            namesVals += "@val" + count + ",";
                            count++;
                            break;
                        default:
                            pars[count] = new SqlParameter("val" + count, SqlDbType.VarChar);
                            pars[count].Value = b.GetType().GetProperty(p.Name).GetValue(b, null);
                            namesVals += "@val" + count + ",";
                            count++;
                            break;
                    }
                }
            }
            insQ.Append(names.Substring(0, names.Length - 1));
            insQ.Append(") VALUES(");
            insQ.Append(namesVals.Substring(0, namesVals.Length - 1));
            insQ.Append(")");
            return Connection.ExecuteNonQuery(Connection.CONN_STRING1, CommandType.Text, insQ.ToString(), true, pars);
        }

        public int Update(BaseObject b, string byName, string byVal)
        {
            StringBuilder insQ = new StringBuilder();
            Type t = b.GetType();
            insQ.Append("UPDATE ");
            insQ.Append(t.Name + " SET ");
            string names = "";
            SqlParameter[] pars = new SqlParameter[t.GetProperties().Length - 1];
            int count = 0;
            foreach (PropertyInfo p in t.GetProperties())
            { 
                if (p.Name != "ID")
                {
                    names += p.Name + "=";
                    switch (p.PropertyType.ToString())
                    {
                        case "System.String":
                            pars[count] = new SqlParameter("val" + count, SqlDbType.VarChar);
                            pars[count].Value = b.GetType().GetProperty(p.Name).GetValue(b, null) as string;
                            names += "@val" + count + ",";
                            count++;
                            break;
                        case "System.Int32":
                            pars[count] = new SqlParameter("val" + count, SqlDbType.Int);
                            pars[count].Value = (Int32)b.GetType().GetProperty(p.Name).GetValue(b, null);
                            names += "@val" + count + ",";
                            count++;
                            break;
                        case "System.Int64":
                            long val = (Int64)b.GetType().GetProperty(p.Name).GetValue(b, null);
                            pars[count] = new SqlParameter("val" + count, SqlDbType.BigInt);

                            if (val == -1)
                                pars[count].Value = DBNull.Value;
                            else
                                pars[count].Value = (Int64)b.GetType().GetProperty(p.Name).GetValue(b, null);
                            names += "@val" + count + ",";
                            count++;
                            break;
                        case "System.DateTime":
                            pars[count] = new SqlParameter("val" + count, SqlDbType.DateTime);
                            pars[count].Value = b.GetType().GetProperty(p.Name).GetValue(b, null);
                            names += "@val" + count + ",";
                            count++;
                            break;
                        case "System.Boolean":
                            names += "'"+(Boolean)b.GetType().GetProperty(p.Name).GetValue(b, null) + "',";
                            break;
                        case "System.Single":
                            names += "'" + (float)b.GetType().GetProperty(p.Name).GetValue(b, null) + "',";
                            break;
                        case "System.Double":
                            names += "'" + (double)b.GetType().GetProperty(p.Name).GetValue(b, null) + "',";
                            break;
                        default:
                            pars[count] = new SqlParameter("val" + count, SqlDbType.VarChar);
                            pars[count].Value = b.GetType().GetProperty(p.Name).GetValue(b, null);
                            names += "@val" + count + ",";
                            count++;
                            break;
                    }
                }
            }
            insQ.Append(names.Substring(0, names.Length - 1));

            if (byName != "")
            {
                insQ.Append(" WHERE " + byName + " = " + byVal);
            }
            return Connection.ExecuteNonQuery(Connection.CONN_STRING1, CommandType.Text, insQ.ToString(), false, pars);
        }

        /// <summary>
        /// pozelno samo za update na stringovi
        /// </summary>
        /// <param name="b"></param>
        /// <param name="colums"></param>
        /// <param name="vals"></param>
        /// <param name="byName"></param>
        /// <param name="byVal"></param>
        /// <returns></returns>
        public int Update(Type b, string[]colums, string[] vals, string byName, string byVal)
        {
            StringBuilder insQ = new StringBuilder();
            Type t = b;
            insQ.Append("UPDATE ");
            insQ.Append(t.Name + " SET ");
            string names = "";
            int len = colums.Length;
            for (int i = 0; i < len; i++)
            {
                if (colums[i] != "ID")
                {
                    names += colums[i] + "=";
                    names += "N'" + vals[i] + "',";
                } 
            }
            insQ.Append(names.Substring(0, names.Length - 1));

            if (byName != "")
            {
                insQ.Append(" WHERE " + byName + " = " + byVal);
            }
            return Connection.ExecuteNonQuery(Connection.CONN_STRING1, CommandType.Text, insQ.ToString(),false, null);
        }

        public long CountOf(Type t)
        {
            string s = "SELECT count(ID) as count from "+t.Name;
            long returnVal;
            DataSet ds = Connection.ExecuteReader(Connection.CONN_STRING1, CommandType.Text, s, null);
            if ( ds.Tables[0].TableName == "Error")
            {
                //Common.CommonFunctions.handleException(dsUser.Tables[0].Rows[0].ItemArray[0].ToString(), log, this.ParentForm);
                return 0;
            }
            if (long.TryParse(ds.Tables[0].Rows[0]["count"].ToString(), out returnVal))
                return returnVal;
            return 0;
        }
    }
}
